package com.lvjunxiong.serviceinterface;

import com.lvjunxiong.serviceinterface.tongxun.*;
import common.CommModel;
import common.database.DBConnectionManager;
import common.database.DBManager;
import common.util.CommUtil;
import utils.PageFactory;
import utils.PageModel;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by lvjunxiong on 2019/2/11.
 */
public class ServiceDao {

    public List<LunbotuModel> gainLunbotuList() {
        List<LunbotuModel> list = new ArrayList<LunbotuModel>();
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql  = "select shl.id,CONCAT('http://wxapp.shuhai.cc/sh_s/sh_img/',shu.filename) as lunbotu_imgurl,concat(left(shl.title,18),'...') as lunbotu_title from sh_lunbotu shl left join sh_upload shu on shu.itemid = shl.id where item_type = 'lunbotu' and shl.status = '1'  order by shl.sort_num";
            list = dbm.getObjectList(LunbotuModel.class,sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return list;
    }

    public LunbotuIdModel gainLunbotuListId(String id) {
        LunbotuIdModel model = null;
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql = "select shl.title,shl.sh_content as text_content,FROM_UNIXTIME(shl.c_time/1000,'%Y-%m-%d %H:%i') as time from sh_lunbotu shl where shl.id = '"+  id+"'";
            model = (LunbotuIdModel) dbm.getObject(LunbotuIdModel.class,sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return model;
    }

    public List<NewmessageModel> new_messageList() {
        List<NewmessageModel> list = new ArrayList<NewmessageModel>();
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql = "select shc.id,CONCAT('http://wxapp.shuhai.cc/sh_s/sh_img/',shu.filename) as imgurl,shc.title,from_unixtime(shc.c_time/1000,'%Y-%m-%d %H:%i') as time from sh_content shc left join sh_upload shu on shu.itemid = shc.id where shu.item_type = 'shuhai' ORDER BY time desc limit 0,5";
            list = dbm.getObjectList(NewmessageModel.class,sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return list;
    }

    
    public List<NewmessageModel> new_messageListgong() {
        List<NewmessageModel> list = new ArrayList<NewmessageModel>();
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql = "select shc.id,CONCAT('http://wxapp.shuhai.cc/sh_s/sh_img/',shu.filename) as imgurl,shc.title,from_unixtime(shc.c_time/1000,'%Y-%m-%d %H:%i') as time from sh_paogao shc left join sh_upload shu on shu.itemid = shc.id where shu.item_type = 'paogao' order by time  desc";
            list = dbm.getObjectList(NewmessageModel.class,sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return list;
    }
    
    
    public PageModel new_messageList_less(PageModel pm) {
        List<NewmessageModel> list = new ArrayList<NewmessageModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String querysql = "select shc.id,CONCAT('http://wxapp.shuhai.cc/sh_s/sh_img/',shu.filename) as imgurl,shc.title,from_unixtime(shc.c_time/1000,'%Y-%m-%d %H:%i') as time from sh_content shc left join sh_upload shu on shu.itemid = shc.id where shu.item_type = 'shuhai' order by time desc";
            String countsql = "select count(*) from (" + querysql + ")t";
            int count = dbm.executeQueryCount(countsql);
            int total = count % pm.getPerPage() == 0 ? count / pm.getPerPage() : count / pm.getPerPage() + 1;
            pm.setTotalPage(total);
            pm.setTotalRecord(count);
            PageFactory pageFactory = new PageFactory();
            String sql = pageFactory.createPageSQL(querysql, pm);
            pageFactory = null;
            list = dbm.getObjectList(NewmessageModel.class, sql);
            pm.setList(list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return pm;
    }



    public List<NewmessageModel> new_messageList_less_title(String title) {
        List<NewmessageModel> list = new ArrayList<NewmessageModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select shc.id,CONCAT('http://wxapp.shuhai.cc/sh_s/sh_img/',shu.filename) as imgurl,shc.title,from_unixtime(shc.c_time/1000,'%Y-%m-%d %H:%i') as time from sh_content shc left join sh_upload shu on shu.itemid = shc.id where shu.item_type = 'shuhai' and shc.title like '%"+ title +"%' order by time desc";
            list = dbm.getObjectList(NewmessageModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public NewmessageIdModel new_messageListId(String id) {
        NewmessageIdModel model = null;
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql = "select shl.id,shl.title,shl.sh_content as text_content,FROM_UNIXTIME(shl.c_time/1000,'%Y-%m-%d %H:%i') as time from sh_content shl where shl.id = '"+ id +"'";
            model = (NewmessageIdModel) dbm.getObject(NewmessageIdModel.class,sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return model;
    }

    public SousouModel queryUser_openid(String openid) {
        SousouModel model = null;
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql = "select openid as result,id as id from sh_openid where openid = '"+ openid +"'";
            model = (SousouModel) dbm.getObject(SousouModel.class,sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return model;
    }

    public int insertUser_openid(String openid, String jtId, String party_id, String userName,String shuhai_phone) {
        int i =0;
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql = "insert into sh_openid(id,openid,shuhai_jtid,shuhai_party_id,shuhai_userName,shuhai_phone) values('"+ CommUtil.getID() +"','"+ openid +"','" + jtId +"','"+ party_id +"','"+ userName +"','"+ shuhai_phone +"')";
            i = dbm.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return i;
    }

    public int updateUser_openid(String openid, String id) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "update sh_openid set openid = '"+ openid +"'  where id = '"+ id +"'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public CommModel gain_Url(String id) {
        CommModel model = null;
        DBManager dbm = null;
        try{
            dbm = new DBManager();
            String sql = "select url as result from sh_fawen where id = '"+ id +"'";
            model = (CommModel) dbm.getObject(CommModel.class,sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            dbm.close();
        }
        return model;
    }

    public PageModel queryFawen(PageModel pm) {
        List<FawenModel> list = new ArrayList<FawenModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
                String querysql = "select id,concat(left(title,15),'...') as title,url,from_unixtime(c_time/1000,'%Y-%m-%d %H:%i') as c_time from sh_fawen order by sort_num";
                String countsql = "select count(*) from (" + querysql + ")t";
                int count = dbm.executeQueryCount(countsql);
                int total = count % pm.getPerPage() == 0 ? count / pm.getPerPage() : count / pm.getPerPage() + 1;
                pm.setTotalPage(total);
                pm.setTotalRecord(count);
                PageFactory pageFactory = new PageFactory();
                String sql = pageFactory.createPageSQL(querysql, pm);
                pageFactory = null;
                list = dbm.getObjectList(FawenModel.class, sql);
            pm.setList(list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return pm;
    }

    public CommModel findUserOpenid(String openid) {
        CommModel model = null;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select sho.openid as result from sh_openid sho where sho.openid = '"+ openid +"'";
            model = (CommModel) dbm.getObject(CommModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return model;
    }

    public RModel findResult(String openid) {
        RModel model = null;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select sho.shuhai_jtid as shuhai_jtid,sho.shuhai_party_id as shuhai_party_id," +
                    "sho.shuhai_userName as shuhai_userName from sh_openid sho where sho.openid = '" + openid + "'";
            model = (RModel) dbm.getObject(RModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return model;
    }

    public int quitLoginUser(String openid) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "delete from sh_openid where openid = '"+ openid +"'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public int deletesh_suoyin() {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "delete from sh_suoyin";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public int deletesh_suoyin_list() {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "delete from sh_suoyin_list";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public List<Tongxun_twoModel> queryTongxun_twoModel() {
        List<Tongxun_twoModel> list = new ArrayList<Tongxun_twoModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id,enabled from sh_suoyin";
            list = dbm.getObjectList(Tongxun_twoModel.class,sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public List<Tongxun_twoModel> queryTongxun_twoModel2() {
        List<Tongxun_twoModel> list = new ArrayList<Tongxun_twoModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id from sh_suoyin";
            list = dbm.getObjectList(Tongxun_twoModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public List<Tongxun_twoListModel> queryF(String id,String party_id) {
        List<Tongxun_twoListModel> list = new ArrayList<Tongxun_twoListModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id_s as id,name from sh_suoyin_list where id = '"+ id +"' and id_s != '"+ party_id +"'";
            list = dbm.getObjectList(Tongxun_twoListModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public List<Tongxun_twoListModel> queryF_s(String id, String party_id, String name) {
        List<Tongxun_twoListModel> list = new ArrayList<Tongxun_twoListModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "";
            if(name.length() == 1){
                System.out.println("字符串的长度是："+ name.length());
                sql += "select id_s as id,name from sh_suoyin_list where id = '" + id + "' and id_s != '" + party_id + "' and name like '%" + name + "%'";
            }else{
                sql += "select id_s as id,name from sh_suoyin_list where id = '" + id + "' and id_s != '" + party_id + "' and name like '%" + name + "%' union " +
                        "select id_s as id,name from sh_suoyin_list where id = '" + id + "' and id_s != '" + party_id + "' and company like '%" + name + "%'";
            }
            list = dbm.getObjectList(Tongxun_twoListModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public CommModel qOpenid(String openid) {
        CommModel model = new CommModel();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select openid as result from sh_openid where openid = '"+ openid +"'";
            model = (CommModel) dbm.getObject(CommModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return model;
    }

    public List<R> qR() {
        List<R> list = new ArrayList<R>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id_s as id from sh_suoyin_list where company = ''";
            list = dbm.getObjectList(R.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public int updatesh_suoyinList(String gain_id1, String s) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "update sh_suoyin_list set company = '"+ s +"' where id_s = '"+ gain_id1 +"'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public CommModel findPhoneNum(String openid) {
        CommModel model = null;
        DBManager dbm = null;
            try {
                dbm = new DBManager();
                String sql = "select shuhai_phone as result from sh_openid where openid = '"+ openid +"'";
                model = (CommModel) dbm.getObject(CommModel.class, sql);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                dbm.close();
            }
        return model;
    }

    public CommModel queryO(String id) {
        CommModel model = null;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select type as result from sh_suoyin_list where id_s = '"+  id+"'";
            model = (CommModel) dbm.getObject(CommModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return model;
    }

    public List<R> bm() {
        List<R> list = new ArrayList<R>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id_s as id from sh_suoyin_list where corp is null or corp = ''";
            list = dbm.getObjectList(R.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public int updateSh_suoyin_listIII(String corp_name, String hq_id) {
        int i =0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "update sh_suoyin_list set corp = '"+ corp_name +"' where id_s = '"+ hq_id +"'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public int updateSh_suoyin_listII(String corp_name, String hq_id) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "update sh_suoyin_list set corp = '' where id_s = '" + hq_id + "'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public int deletePhone() {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "delete from sh_phone";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public List<R> bmAll() {
        List<R> list = new ArrayList<R>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id_s as id from sh_suoyin_list";
            list = dbm.getObjectList(R.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public int insertP(String gain_contactNumber, String gain_purposeName, String id) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "insert into sh_phone(id,phone,type,id_s) values('"+ CommUtil.getID() +"','"+ gain_contactNumber +"','"+ gain_purposeName +"','"+ id+"');";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public int updateS_list(String flag,String id) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "update sh_suoyin_list set flag = '"+ flag +"' where id_s = '"+ id +"'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public List<PhoneModel> queryPhone(String id) {
        List<PhoneModel> list = new ArrayList<PhoneModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select phone as phonenum,type as phonetype from sh_phone where id_s = '"+ id +"'";
            list = dbm.getObjectList(PhoneModel.class,sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public PhoneModel queryCorpOrCompany(String id) {
        PhoneModel model = null;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select company as phonenum,corp as phonetype from sh_suoyin_list where id_s = '"+ id +"'";
            model = (PhoneModel)dbm.getObject(PhoneModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return model;
    }

    public ResultModel queryResultModel(String party_id) {
        ResultModel model = null;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id_s as id_s,corp as corpname from sh_suoyin_list where id_s = '"+ party_id +"'";
            model = (ResultModel) dbm.getObject(ResultModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return model;
    }


    public List<PhoneUpdateModel> queryPP(String userid) {
        List<PhoneUpdateModel> list = new ArrayList<PhoneUpdateModel>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select id,phone as phonenum,type as phonetype,id_s as id_s from sh_phone where id_s = '"+ userid +"'";
            list = dbm.getObjectList(PhoneUpdateModel.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public int updateCC(String party_id,String corpname) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "update sh_suoyin_list set corp = '" + corpname + "' where id_s = '" + party_id + "'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public List<Sh_phoneModel_S> queryOnePhone(String party_id) {
        List<Sh_phoneModel_S> list = new ArrayList<Sh_phoneModel_S>();
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "select type,phone,id_s,id from sh_phone where id_s = '"+ party_id +"'";
            list = dbm.getObjectList(Sh_phoneModel_S.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return list;
    }

    public int insertShouji(String party_id, String phonenum, String phonetype) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "insert into sh_phone(id,phone,type,id_s) values('"+ CommUtil.getID() +"','"+ phonenum +"','"+ phonetype +"','"+ party_id +"')";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }

    public int updateShouji(String type, String party_id,String phone) {
        int i = 0;
        DBManager dbm = null;
        try {
            dbm = new DBManager();
            String sql = "update sh_phone set phone = '"+ phone +"' where type = '"+ type +"' and id_s = '"+ party_id +"'";
            i = dbm.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.close();
        }
        return i;
    }
}
